package com.hzxy.yiyid.web.tool;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class ExcelTool {

    public static final String RESULT_MSG_SUCCESS = "导入成功";
    public static final String RESULT_MSG_NULL = "EXCEL文件为空";
    public static final String RESULT_MSG_LARGE = "EXCEL文件大小超过3M";
    public static final String RESULT_MSG_WRONG = "EXCEL模板错误，请下载正确的模板";
    public static final String RESULT_MSG_LIMIT = "数据量超过最大限制，最多导入100000条";
    public static final String RESULT_MSG_EMPTY = "EXCEL文件数据为空";

    private static final Logger LOGGER = LoggerFactory.getLogger(ExcelTool.class);

    /*
     * @Author RAICOM2023
     * @Description //判断头文件是否与模板一致
     * @Date 15:04 2023/5/2
     * @Param [row, templateMap]
     * @return boolean
     **/
    private static boolean header(Row row, Map<Character, String> templateMap){
        if((row.getLastCellNum()-row.getFirstCellNum()) != templateMap.size()){
            return false;
        }
        for(int i = row.getFirstCellNum(); i < row.getLastCellNum();i ++){
            row.getCell(i).setCellType(CellType.STRING);
            String cellValue = row.getCell(i).getStringCellValue();
            Character key = (char)('A' + i);
            if(templateMap.get(key).equals(cellValue)){
                continue;
            }else{
                return false;
            }
        }
        return true;
    }

    /*
     * @Author RAICOM2023
     * @Description //取excel每行数据
     * @Date 15:04 2023/5/2
     * @Param [row, size]
     * @return java.util.List<java.lang.String>
     **/
    private static List<String> convertRowtoObject(Row row, int size) {
        ArrayList<String> list = new ArrayList<String>();
        for(int i = row.getFirstCellNum(); i < size;i ++){
            Cell cell = row.getCell(i);
            if(cell == null){
                list.add("null");
            }else{
                cell.setCellType(CellType.STRING);
                if(StringUtils.isBlank(cell.getStringCellValue().trim())){
                    list.add("null");
                }else{
                    list.add(cell.getStringCellValue());
                }
            }
        }
        return list;
    }

    /*
     * @Author RAICOM2023
     * @Description //导入-敏感词
     * @Date 15:04 2023/5/2
     * @Param [file, templateMap, type]
     * @return java.util.List
     **/
    public static List importExcelFile_word(MultipartFile file, Map<Character, String> templateMap) throws Exception {
        InputStream input = null;
        Workbook wk = null;
        List result = new ArrayList();
        try{
            input = file.getInputStream();
            if(input.available()<=0){
                result.add(RESULT_MSG_NULL);
                return result;
            }
            // 文件大小大于3M
            int fileMaxSize = 1024*1024*3;
            if(file.getSize() > fileMaxSize){
                result.add(RESULT_MSG_LARGE);
                return result;
            }
            wk = WorkbookFactory.create(input);
            Sheet sheet = null;
            List<Integer> sheetIndexList = new ArrayList<>();
            int numberOfSheets = wk.getNumberOfSheets();
            for(int i=0; i<numberOfSheets; i++){
            	sheet = wk.getSheetAt(i);
            	if(sheet!=null){
            		Row row0 = sheet.getRow(0);
            		if(!header(row0, templateMap)){
            			result.add(RESULT_MSG_WRONG);
                        return result;
            		}
            		int lastRowNum = sheet.getLastRowNum();
                    if(lastRowNum >= 1){
                    	sheetIndexList.add(i);
                    }
            	}
            }
            if(sheetIndexList.size()==0){
            	result.add(RESULT_MSG_EMPTY);
                return result;
            }
            List<List<String>> dataList = new ArrayList<List<String>>();
            Row row = null;
            for(int i : sheetIndexList){
            	sheet = wk.getSheetAt(i);
            	int lastRowNum = sheet.getLastRowNum();
            	for(int j=1; j<=lastRowNum; j++){
            		row = sheet.getRow(j);
            		List<String> list= convertRowtoObject(row,templateMap.size());
            		dataList.add(list);
            	}
            }
            result.add(RESULT_MSG_SUCCESS);
            result.add(dataList);
            return result;
        }finally{
            if(input!=null){
                input.close();
            }
        }
    }


    /*
     * @Author RAICOM2023
     * @Description //导入
     * @Date 15:04 2023/5/2
     * @Param [file, templateMap]
     * @return java.util.List
     **/
    public static List importExcelFile(MultipartFile file, Map<Character, String> templateMap) throws Exception {
        InputStream input = null;
        Workbook wk = null;
        List result = new ArrayList();
        try{
            input = file.getInputStream();
            if(input.available()<=0){
                result.add(RESULT_MSG_NULL);
                return result;
            }
            // 文件大小大于100M
            int fileMaxSize = 1024*1024*100;
            if(file.getSize() > fileMaxSize){
                result.add(RESULT_MSG_LARGE);
                return result;
            }
            wk = WorkbookFactory.create(input);
            Sheet sheet = null;
            List<Integer> sheetIndexList = new ArrayList<>();
            int numberOfSheets = wk.getNumberOfSheets();
            for(int i=0; i<numberOfSheets; i++){
                sheet = wk.getSheetAt(i);
                if(sheet!=null){
                    Row row0 = sheet.getRow(0);
                    if(row0==null)
                        continue;
                    if(!header(row0, templateMap)){
                        result.add(RESULT_MSG_WRONG);
                        return result;
                    }
                    int lastRowNum = sheet.getLastRowNum();
                    if(lastRowNum >= 1){
                        sheetIndexList.add(i);
                    }
                }
            }
            if(sheetIndexList.size()==0){
                result.add(RESULT_MSG_EMPTY);
                return result;
            }
            List<List<String>> dataList = new ArrayList<List<String>>();
            Row row = null;
            for(int i : sheetIndexList){
                sheet = wk.getSheetAt(i);
                int lastRowNum = sheet.getLastRowNum();
                for(int j=1; j<=lastRowNum; j++){
                    row = sheet.getRow(j);
                    List<String> list= convertRowtoObject(row,templateMap.size());
                    dataList.add(list);
                }
            }
            result.add(RESULT_MSG_SUCCESS);
            result.add(dataList);
            return result;
        }finally{
            if(null!=input){
                input.close();
            }
        }
    }

    /*
     * @Author RAICOM2023
     * @Description //流形式导入
     * @Date 9:48 2023/5/5
     * @Param [input, templateMap]
     * @return java.util.List
     **/
    public static List importExcelFile(InputStream input, Map<Character, String> templateMap) throws Exception {
        Workbook wk = null;
        List result = new ArrayList();
        try{
            if(input.available()<=0){
                result.add(RESULT_MSG_NULL);
                return result;
            }
            wk = WorkbookFactory.create(input);
            Sheet sheet = null;
            List<Integer> sheetIndexList = new ArrayList<>();
            for(int i=0; i<wk.getNumberOfSheets(); i++){
                sheet = wk.getSheetAt(i);
                if(sheet!=null){
                    Row row0 = sheet.getRow(0);
                    if(!header(row0, templateMap)){
                        result.add(RESULT_MSG_WRONG);
                        return result;
                    }
                    int lastRowNum = sheet.getLastRowNum();
                    if(lastRowNum >= 1){
                        sheetIndexList.add(i);
                    }
                    /*if(lastRowNum > EXCLE_LIMIT){
                        result.add(RESULT_MSG_LIMIT);
                        return result;
                    }*/
                }
            }
            if(sheetIndexList.size()==0){
                result.add(RESULT_MSG_EMPTY);
                return result;
            }
            List<List<String>> dataList = new ArrayList<List<String>>();
            Row row = null;
            for(int i : sheetIndexList){
                sheet = wk.getSheetAt(i);
                int lastRowNum = sheet.getLastRowNum();
                for(int j=1; j<=lastRowNum; j++){
                    row = sheet.getRow(j);
                    List<String> list= convertRowtoObject(row,templateMap.size());
                    dataList.add(list);
                }
            }
            result.add(RESULT_MSG_SUCCESS);
            result.add(dataList);
            return result;
        }finally{
            if(input!=null){
                input.close();
            }
        }
    }

    /*
     * @Author RAICOM2023
     * @Description //导出
     * @Date 9:48 2023/5/5
     * @Param [list, header, infoList]
     * @return org.apache.poi.ss.usermodel.Workbook
     **/
    public static Workbook exportFile(List<Map<String, Object>> list, List<String> header,
                                      List<String> infoList) throws Exception {
        SXSSFWorkbook sb = new SXSSFWorkbook();
        Sheet sheet = sb.createSheet();
        Row row = sheet.createRow(0);
        Cell cell = null;
        Font font = sb.createFont();
        font.setBold(true);
        CellStyle cellStyle = sb.createCellStyle();
        cellStyle.setFont(font);
        //创建标题
        for(int i=0;i<header.size();i++){
            cell = row.createCell(i);
            cell.setCellType(CellType.STRING);
            cell.setCellValue(header.get(i));
            cell.setCellStyle(cellStyle);
        }

        Map<String, Object> map = null;
        Cell dataCell = null;
        for(int i=0;i<list.size();i++){
            row = sheet.createRow(i + 1);
            map = list.get(i);
            for(int j=0;j<infoList.size();j++){
                String info = infoList.get(j);
                dataCell = row.createCell(j);
                dataCell.setCellType(CellType.STRING);
                dataCell.setCellValue(map.get(info)==null ? null : map.get(info).toString());
            }
        }
        for(int i=0;i<header.size();i++){
            sheet.setColumnWidth(i, 6000);
        }
        return sb;
    }
    
    /*
     * @Author RAICOM2023
     * @Description //模板下载
     * @Date 14:47 2023/5/2
     * @Param [header]
     * @return org.apache.poi.ss.usermodel.Workbook
     **/
    public static Workbook downloadTemplate(List<String> header) throws Exception {
        SXSSFWorkbook sb = new SXSSFWorkbook();
        Sheet sheet = sb.createSheet();
        Row row = sheet.createRow(0);
        Cell cell = null;
        Font font = sb.createFont();
        font.setBold(true);
        CellStyle cellStyle = sb.createCellStyle();
        cellStyle.setFont(font);
        //创建标题
        for(int i=0;i<header.size();i++){
            cell = row.createCell(i);
            cell.setCellValue(header.get(i));
            cell.setCellStyle(cellStyle);
        }
        for(int i=0;i<header.size();i++){
            sheet.setColumnWidth(i, 6000);
        }
        return sb;
    }

    /*
     * @Author RAICOM2023
     * @Description //workbook转inputStream
     * @Date 14:46 2023/5/2
     * @Param [workbook]
     * @return byte[]
     **/
    public static byte[] workbook2InputStream(Workbook workbook) {
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        try {
            workbook.write(baos);
            baos.flush();
            byte[] aa = baos.toByteArray();
            return aa;
        } catch (IOException e) {
            LOGGER.error("Data inputting to excel error.", e);
        } finally {
            if(null!=baos){
                try {
                    baos.close();
                } catch (Exception e) {
                    LOGGER.error("Failed for byte array output stream to release resource.");
                }
                baos=null;
            }
        }
        return null;
    }

}
